*Author: Luo Mi
*Auditer: Jason Premo
*Date Audited: 6/14/16-8/1/16
*Modified by David to add business income and correct rent income variable, 6/14/2016. 
*Purpose: Creates the PSID means, for the purposes of comparison, in Scott's DCPC consumption paper.
* Heavily modified by Jason again on 7/15/2016 to automate output and do some more general auditing.

*Filepath: /shared/sdsjxs/Townsend/scripts/psid_clean.do


clear all
set more off
set maxvar 20000
cap log close

*log using "/shared/sdsjxs/Townsend/logfiles/psid_means.log", replace

*AUDIT: Updated file paths, changed some names.
local in_path "/shared/sdsjxs/Townsend/rawdata/psid"
global out_path "/shared/sdsjxs/Townsend/results"
local data_gen no


cap program drop output_all
program define output_all
	args remove sheetname income
	
	drop ER* hhid 
	qui describe, varlist
	egen double all = rowtotal(`r(varlist)')
	replace all = all `remove'

	if "`income'" == "yes" {
		gen all_censored = all if all <= 200000
	}

	
	qui describe, varlist
	local meanlist = ""
	local medianlist = ""

	*Not really using the medians, except for the totals
	foreach variable in `r(varlist)' {
		if "`variable'" == "aw" {
			continue
			}
		local tempname1 = "mean_`variable' = `variable' "
		local tempname2 = "med_`variable' = `variable' "
		local meanlist = "`meanlist'"+"`tempname1'"
		local medianlist = "`medianlist'"+"`tempname2'"
		}
	
	preserve
	
	collapse (mean) `meanlist' (p50) med_all=all [aw = aw]
	xpose, varname clear
	rename _varname varname
	order varname
	replace v1 = round(v1)

	export excel using "$out_path/balance_sheet.xlsx", sheet("psid_`sheetname'", replace)
end



******************************** PSID 2013 *************************************

if "`data_gen'" == "yes" {
	use "`in_path'/psid2013", clear

*Family weight as analytic weight
*Turning "family units" into households for better comparison
*Family ID: ER53009
	gen weight 	= ER58257
	
	rename ER58245 hhid 
	gen family_weight = weight*ER53016
	collapse (sum) family_weight E*, by(hhid) fast
	gen aw = family_weight/ER53016
	drop family_weight



*Sick of doing the above collapse. This saves time.
	save "`in_path'/psid_new.dta", replace
	}

use "`in_path'/psid_new.dta", clear

tempfile start
save "`start'", replace


********************************* 1. Assets ************************************


*Combined value of Checking or savings accounts, Money market funds,
*       Certificates of deposit, Government bonds, and Treasury bills
gen bankacct 	= ER54661 if ER54661 < 999999998
*Current value of all non-IRA stock
gen stock 	= ER54634 if ER54634 > -999999999 & ER54634 < 999999998
*Current value of IRA/Annuity
gen ira 	= ER54655 if ER54655 < 999999998

*AUDIT: Amount in Pension Now for Head of household
gen pension1 	= ER54836 if ER54836 < 999999998
*AUDIT: Amount in Pension Now, Spouse
gen pension2 	= ER55052 if ER55052 < 999999998 
egen pension 	= rowtotal(pension1 pension2 ira)
*drop ira pension1 pension2

*AUDIT: Net value of life insurance, valuable collections, rights in trust/estate
*Pretty sure there's no way to turn this back into a gross value.
gen insur_life 	= ER54682 if ER54682 > -999999999 & ER54682 < 999999998

*AUDIT: Value of Primary Residence
gen home_prim 	= ER53030 if ER53030 < 9999998

*AUDIT: Value of Second home
gen home_second = ER54612 if ER54612 < 999999998

*AUDIT: Value of Farm or Business
gen business = ER54625 if ER54625 < 999999998

*AUDIT: Net value of vehicle, made gross
gen loan_vehic1 = ER53761 if ER53761 < 999998
gen loan_vehic2 = ER53685 if ER53685 < 999998
gen loan_vehic3 = ER53809 if ER53809 < 999998
egen loan_vehic = rowtotal(loan_vehic1 loan_vehic2 loan_vehic3)
gen vehic = ER54620 + loan_vehic if ER54620 > -1e8 & ER54620 < 999999998
drop loan_vehic*


*** This is a cash flow variable. I'm putting it here to avoid creating unncessary worksheets.
*gen pension_contrib1 = ER54821 if ER54821 < 9999998
*replace pension_contrib1 = pension_contrib1*52 if ER54822 == 3
*replace pension_contrib1 = pension_contrib1*26 if ER54822 == 4
*replace pension_contrib1 = pension_contrib1*12 if ER54822 == 5
gen pension_contrib = ER54825 if ER54825 < 9999998
replace pension_contrib = pension_contrib*52 if ER54826 == 3
replace pension_contrib = pension_contrib*26 if ER54826 == 4
replace pension_contrib = pension_contrib*12 if ER54826 == 5
*egen pension_contrib = rowtotal(pension_contrib*)
*drop pension_contrib1 pension_contrib2


output_all " - aw - pension_contrib" "assets"


*********************************** 2. Liabilities *****************************

use `start', clear

*AUDIT: Credit card debt, not including debt from convenience use
gen creditcard  =  ER54687 if ER54687 < 9999998

*AUDIT: Remaining principal on primary mortgage.
*I don't see a variable for remaining interest, so this is as good as it gets.
gen mortgage_prim 	= ER53048 if ER53048 < 9999998

*AUDIT: Remaining principal on secondary or other mortgage.
gen mortgage2 	= ER53069 if ER53069 < 9999998
gen mortgage3 = ER54616 if ER54616 < 9999998
egen mortgage_other = rowtotal(mortgage2 mortgage3)

drop mortgage2 mortgage3

*AUDIT: Remaining principal on car loans
gen loan_vehic1 = ER53761 if ER53761 < 999998
gen loan_vehic2 = ER53785 if ER53785 < 999998
gen loan_vehic3 = ER53809 if ER53809 < 999998
egen loan_vehic = rowtotal(loan_vehic1 loan_vehic2 loan_vehic3)
drop loan_vehic1 loan_vehic2 loan_vehic3

*AUDIT: Student Loans
gen loan_educ 	= ER54697 if ER54697 < 9999998

*AUDIT: Loans from relatives
gen loan_fam 	= ER54712 if ER54712 < 9999998

*AUDIT: Other debts (medical bills + legal bills + other debt)
gen loan_other1 = ER54702 if ER54702 < 9999998
gen loan_other2 = ER54707 if ER54707 < 9999998
gen loan_other3 = ER54718 if ER54718 < 9999998
egen loan_other 	= rowtotal(loan_other*)
drop loan_other1 loan_other2 loan_other3

output_all " - aw" "liabilities"

*********************************** 3. Income **********************************

use `start', clear

// NOTES: '1' denotes HEAD, '2' denotes WIFE

*AUDIT: Wages
gen t_wageinc1 	= ER53935 if ER53935 < 9999998
gen t_wageinc2 	= ER54309 if ER54309 < 9999998

*AUDIT: Bonuses, overtime, tips, and commissions
gen t_bonusinc1 	= ER53939 if ER53939 < 999998
gen t_overtimeinc1 = ER53942 if ER53942 < 999998
gen t_tipinc1 	= ER53945 if ER53945 < 999998
gen t_comminc1 	= ER53948 if ER53948 < 999998
egen t_totbonusinc1 	= rowtotal(t_bonusinc1 t_overtimeinc1 t_tipinc1 t_comminc1)

egen wageinc 	= rowtotal(t_wageinc1 t_wageinc2 t_totbonusinc1)

*AUDIT: Professional/Trade Income
gen t_tradeinc1	= ER53955 if ER53955 < 9999998
replace t_tradeinc1 = t_tradeinc1*12 if ER53956 == 5

*AUDIT: Net Income from farming or gardening
gen t_mktgarinc1 	= ER53972 if ER53972 > -999998 & ER53972 < 9999998
replace t_mktgarinc1 = t_mktgarinc1*12 if ER53973 == 5
replace t_mktgarinc1 = t_mktgarinc1*(365/14) if ER53973 == 3

egen tradeinc 	= rowtotal(t_tradeinc1 t_mktgarinc1)

*AUDIT: Extra Job income, other labor income
gen t_xtrajbinc1	= ER53989 if ER53989 > -99998 & ER53989 < 999998
gen t_otrlabinc1	= ER53951 if ER53951 < 999998
egen olabinc1 	= rowtotal(t_xtrajbinc1 t_otrlabinc1)

* Income form business
gen businc 	= ER58012 if ER58012 > -999998 & ER58012 < 9999998

*AUDIT: Net Rent Income
*DAVID AUDIT: Replaced "ER53992 > 99998" with "ER53992 > -99998"
gen t_rentinc1 	= ER53992 if  ER53992 > -99998 & ER53992 < 999998
replace t_rentinc1 = t_rentinc1 *(365/14) if ER53993 == 4
replace t_rentinc1 = t_rentinc1 * 12 if ER53993 == 5
gen t_rentinc2 	= ER54345 if ER54345 > -99998 & ER54345 < 999998
replace t_rentinc2 = t_rentinc2*12 if ER54346 == 5
egen rentinc 	= rowtotal(t_rentinc1 t_rentinc2)

*AUDIT: Income from Dividends
gen t_divinc1 	= ER54009 if ER54009 < 999998
replace t_divinc1 = t_divinc1 * 12 if ER54010 == 5
gen t_divinc2 	= ER54362 if ER54362 < 999998
replace t_divinc2 = t_divinc2 * 12 if ER54363 == 5
egen divinc = rowtotal(t_divinc*)

*AUDIT: Income from interest
gen t_intinc1 	= ER54026 if ER54026 < 999998
replace t_intinc1 = t_intinc1 * 12 if ER54027 == 5
gen t_intinc2 	= ER54379 if ER54379 < 999998
replace t_divinc2 = t_divinc2 * 12 if ER54380 == 5
egen intinc 	= rowtotal(t_intinc1 t_intinc2)

*AUDIT: Income from Trusts Funds
gen t_trustinc1 	= ER54043 if ER54043 < 999998
replace t_trustinc1 = t_trustinc1 * 12 if ER54044 == 5
gen t_trustinc2	= ER54396 if ER54396 < 999998
replace t_trustinc2 = t_trustinc2 * 12 if ER54397 == 5
egen trustinc 	= rowtotal(t_trustinc1 t_trustinc2)

*AUDIT: Income from ADC/TANF (welfare)
gen t_tanfinc1 	= ER54060 if ER54060 < 999998
replace t_tanfinc1 = t_tanfinc1*(365/14)  if ER54061 == 4
replace t_tanfinc1 = t_tanfinc1 * 12 if ER54061 == 5
gen t_tanfinc2	= ER54430 if ER54430 < 999998
replace t_tanfinc2 = t_tanfinc2 * 12 if ER54431 == 5
egen tanfinc 	= rowtotal(t_tanfinc*)

*AUDIT: SSI Income
gen t_ssiinc1 	= ER54077 if ER54077 < 999998
replace t_ssiinc1 = t_ssiinc1 * 12 if ER54078 == 5
gen t_ssiinc2 	= ER54414 if ER54414 < 999998
replace t_ssiinc2 = t_ssiinc2 * 12 if ER54415 == 5
egen ssiinc 	= rowtotal(t_ssiinc1 t_ssiinc2)


*AUDIT: Other welfare income
gen t_otrwelinc1	= ER54093 if ER54093 < 999998
replace t_otrwelinc1 = t_otrwelinc1*(365/14) if ER54094 == 4
replace t_otrwelinc1 = t_otrwelinc1*12 if ER54094 == 5
gen t_otrwelinc2 	= ER54462 if ER54462 < 999998
replace t_otrwelinc2 =t_otrwelinc2 * 12 if ER54463 == 5

egen otrwelinc 	= rowtotal(t_otrwelinc1 t_otrwelinc2)

*AUDIT: VA Pension Income
gen vapeninc1 	= ER54113 if ER54113 < 999998
replace vapeninc1 = vapeninc1 * 12 if ER54114 == 5

*AUDIT: Non-VA Pension Income
gen t_pensioninc1 	= ER54129 if ER54129 < 999998
replace t_pensioninc1 	= t_pensioninc1 * 12 if ER54130 == 5
gen t_pensioninc2 	= ER54478 if ER54478 < 999998
replace t_pensioninc2 	= t_pensioninc2 * 12 if ER54479 == 5

egen pensioninc	= rowtotal(t_pensioninc1 t_pensioninc2)

*AUDIT: Annuity Income
gen t_annuiinc1	= ER54145 if ER54145 < 999998
replace t_annuiinc1 = t_annuiinc1 * 12 if ER54146 == 5
gen t_annuiinc2 	= ER54494 if ER54494 < 999998
replace t_annuiinc2 = t_annuiinc2 * 12 if ER54495 == 5

egen annuiinc 	= rowtotal(t_annuiinc*)


*AUDIT: Other Pension Income (it is not clear from the documentation how this differs from Non-VA Pensions,
*       but it doesn't matter because there are no non-zero responses).
gen t_otrpeninc1 	= ER54161 if ER54161 < 999998
gen t_otrpeninc2 	= ER54526 if ER54526 < 999998
replace t_otrpeninc2 = t_otrpeninc2 * 12 if ER54527 == 5

egen otrpeninc 	= rowtotal(t_otrpeninc1 t_otrpeninc2)

*AUDIT: IRA Income
gen t_irainc1 	= ER54177 if ER54177 < 999998
replace t_irainc1 = t_irainc1 * 12 if ER54178 == 5
gen t_irainc2 	= ER54510 if ER54510 < 999998
replace t_irainc2 = t_irainc2 * 12 if ER54511 == 5

egen irainc 	= rowtotal(t_irainc*)


*AUDIT: Unemployment Income
gen t_unempinc1 	= ER54194 if ER54194 < 999998
replace t_unempinc1 = t_unempinc1 *52 if ER54195 == 3
replace t_unempinc1 = t_unempinc1*(365/14) if ER54195 == 4
replace t_unempinc1 = t_unempinc1 * 12 if ER54195 == 5
gen t_unempinc2 	= ER54313 if ER54313 < 999998
replace t_unempinc2 = t_unempinc2 * 52 if ER54314 == 3
replace t_unempinc2 = t_unempinc2 * (365/14) if ER54314 == 4
replace t_unempinc2 = t_unempinc2 * 12 if ER54314 == 5
egen unempinc 	= rowtotal(t_unempinc1 t_unempinc2)

*AUDIT: Worker's Comp Income
gen t_compinc1 	= ER54210 if ER54210 < 999998
replace t_compinc1 = t_compinc1 * 52 if ER54211 == 3
replace t_compinc1 = t_compinc1*(365/14) if ER54211 == 4
replace t_compinc1 = t_compinc1 * 12 if ER54211 == 5
gen t_compinc2 	= ER54329 if ER54329 < 999998
replace t_compinc2 = t_compinc2 * 52 if ER54330 == 3
replace t_compinc2 = t_compinc2 * (365/14) if ER54330 == 4
replace t_compinc2 = t_compinc2 * 12 if ER54330 == 5
egen compinc 	= rowtotal(t_compinc*)


*AUDIT: Child Support Income
gen t_childinc1	= ER54226 if ER54226 < 999998
replace t_childinc1 = t_childinc1 * 52 if ER54227 == 3
replace t_childinc1 = t_childinc1*(365/14) if ER54227 == 4
replace t_childinc1 = t_childinc1 * 12 if ER54227 == 5
gen t_childinc2 	= ER54446 if ER54446 < 999998
replace t_childinc2 = t_childinc2 * 52 if ER54447 == 3
replace t_childinc2 = t_childinc2 * 12 if ER54447 == 5
egen childinc 	= rowtotal(t_childinc*)

*AUDIT: Alimony Income
gen aliminc1 	= ER54242 if ER54242 < 999998
replace aliminc1 = aliminc1 * 12 if ER54243 == 5

*AUDIT: Help from Relatives
gen t_hrelinc1 	= ER54258 if ER54258 < 999998
replace t_hrelinc1 = t_hrelinc1 * 52 if ER54259 == 3
replace t_hrelinc1 = t_hrelinc1 *(365/14) if ER54259 == 4
replace t_hrelinc1 = t_hrelinc1 * 12 if ER54259 == 5
gen t_hrelinc2 	= ER54542 if ER54542 < 999998
replace t_hrelinc2 = t_hrelinc2 * 52/2 if ER54543 == 4
replace t_hrelinc2 = t_hrelinc2 * 12 if ER54543 == 5
egen hrelinc = rowtotal(t_hrelinc*)


*AUDIT: Help from Others
gen t_hotrinc1 	= ER54274 if ER54274 < 999998
replace t_hotrinc1 = t_hotrinc1 * 52 if ER54275 == 3
replace t_hotrinc1 = t_hotrinc1 * (365/14) if ER54275 == 3
replace t_hotrinc1 = t_hotrinc1 * 12 if ER54275 == 3
gen t_hotrinc2 = ER54558 if ER54558 < 999998
replace t_hotrinc2 = t_hotrinc2 * 12 if ER54559 == 5

egen hotrinc = rowtotal(t_hotrinc*)

*AUDIT: Other Income
gen t_otrinc1 	= ER54290 if ER54290 < 999998
replace t_otrinc1 = t_otrinc1 * (365/7) if ER54291 == 3
replace t_otrinc1 = t_otrinc1 * (365/14) if ER54291 == 4
replace t_otrinc1 = t_otrinc1 * 12 if ER54291 == 5
gen t_otrinc2 = ER54574 if ER54574 < 999998
replace t_otrinc2 = t_otrinc2 * (365/7) if ER54575 == 3
replace t_otrinc2 = t_otrinc2 * 12 if ER54575 == 5

egen otrinc = rowtotal(t_otrinc1 t_otrinc2)

*Government subsidy
gen gov_heat_sub = ER53133 if ER53133 < 9998

*AUDIT: Lump Sum Income
gen adjinc 	= ER54590 if ER54590 < 999998

drop t_*

egen intdiv = rowtotal(intinc divinc trustinc annuiinc)
egen govtran = rowtotal(tanfinc ssiinc otrwelinc vapeninc1 unempinc gov_heat_sub)
egen busitran = rowtotal(compinc)
egen otherinc = rowtotal(pensioninc otrpeninc irainc childinc aliminc1 hrelinc hotrinc otrinc)

egen tot_inc = rowtotal(intinc divinc trustinc annuiinc tanfinc ssiinc otrwelinc vapeninc1 unempinc compinc pensioninc otrpeninc irainc childinc aliminc1 hrelinc hotrinc otrinc gov_heat_sub)

gen tot_inc_cen = tot_inc if tot_inc <= 200000

drop tot_inc tot_inc_cen intinc divinc trustinc annuiinc tanfinc ssiinc otrwelinc vapeninc1 unempinc compinc pensioninc otrpeninc irainc childinc aliminc1 hrelinc hotrinc otrinc gov_heat_sub

output_all " - aw" "income" yes


******************************** 4. Expenses ***********************************

*AUDIT: This code is superseded by some code created by David Zhang.
*I have moved the file to the same directory as this one and renamed it psid_consumption.do

do /shared/sdsjxs/Townsend/scripts/psid_consumption.do


*log close

/*

******************************** 4.1 Food  *************************************

// NOTES: '1' denotes food consumption w/ food stamps; '2' denotes w/o food stamps

*AUDIT: These numbers are biased downwards because of the food stamp/no food stamp
*    separation into two variables, as all the people who do not use food stamps
gen food_home1 	= ER53725 if ER53725 < 99998
replace food_home1 = food_home1 * (365/7) if ER53726 == 3
replace food_home1 = food_home1 * (365/14) if ER53726 == 4
replace food_home1 = food_home1 * 12 if ER53726 == 5

gen food_home2 	= ER53735 if ER53735 < 99998
replace food_home2 = food_home2 * (365/7) if ER53736 == 3
replace food_home2 = food_home2 * (365/14) if ER53736 == 4
replace food_home2 = food_home2 * 12 if ER53736 == 5

egen food_home 	= rowtotal(food_home1 food_home2)

gen food_deli1 	= ER53729 if ER53729 < 99998
replace food_deli1 = food_deli1 * (365/7) if ER53730 == 3
replace food_deli1 = food_deli1 * (365/14) if ER53730 == 4
replace food_deli1 = food_deli1 * 12 if ER53730 == 5

gen food_deli2 	= ER53739 if ER53739 < 99998
// tab ER53740 - THIS VARIABLE IS PROBLEMATIC: 8350 REPORTING USING FOOD STAMPS
*AUDIT: Not everybody who responds with 0 necessarily used food stamps. Some
* just didn't get food delivered. The documentation makes this clear.
replace food_deli2 = food_deli2 * (365/7) if ER53740 == 3
replace food_deli2 = food_deli2 * (365/14) if ER53740 == 4
replace food_deli2 = food_deli2 * 12 if ER53740 == 5

egen food_deli 	= rowtotal(food_deli1 food_deli2)

gen food_out1 	= ER53732 if ER53732 < 99998
replace food_out1 = food_out1 * (365/7) if ER53733 == 3
replace food_out1 = food_out1 * (365/14) if ER53733 == 4
replace food_out1 = food_out1 * 12 if ER53733 == 5

gen food_out2 	= ER53742 if ER53742 < 99998
// tab ER53743 - STILL A BIT PROBLEMATIC
*AUDIT: See above.
replace food_out2 = food_out2 * (365/7) if ER53743 == 3
replace food_out2 = food_out2 * (365/14) if ER53743 == 4
replace food_out2 = food_out2 * 12 if ER53743 == 5

egen food_out 	= rowtotal(food_out1 food_out2)

egen food = rowtotal(food_home food_deli food_out)
*AUDIT: About 3% of people report zero dollars in food expenditure...
sum food [w = aw], d

****************************** 4.2 Utilities ***********************************

*AUDIT: Note that fuel1, electricity, and fuel2 do not double-count.
gen fuel1 	= ER53115 if ER53115 < 9998 
replace fuel1 = fuel1 * (365/7) if ER53116 == 3
replace fuel1 = fuel1 * (365/14) if ER53116 == 4
replace fuel1 = fuel1 * 12 if ER53116 == 5

gen electricity = ER53117 if ER53117 < 9998
replace electricity = electricity * 12 if ER53118 == 5

gen fuel2 	= ER53119 if ER53119 < 9998
replace fuel2 	= fuel2 * 12 if ER53120 == 5

egen fuel 	= rowtotal(fuel1 fuel2)

gen water 	= ER53121 if ER53121 < 9998
replace water = water * 4 if ER53122 == 4
replace water = water * 12 if ER53122 == 5

gen tele 	= ER53123 if ER53123 < 9998
replace tele 	= tele * 12 if ER53124 == 5

gen otrutil 	= ER53127 if ER53127 < 998
replace otrutil = otrutil * 12 if ER53128 == 5

gen furnish 	= ER53838 if ER53838 < 999998
replace furnish = furnish * 12 if ER53839 == 5

gen homerepair 	= ER53833 if ER53833 < 999998
replace homerepair = homerepair * 12 if ER53834 == 5

*AUDIT: Added the stuff below, which was excluded
gen rent = ER53087 if ER53087 < 99998
replace rent = rent * 365 if ER53088 == 2
replace rent = rent * (365/7) if ER53088 == 3
replace rent = rent * (365/14) if ER53088 == 4
replace rent = rent * 12 if ER53088 == 5

*AUDIT: This number also includes taxes and insurance for some people
*  (included taxes for 67%, includes insurance for 63%)
gen mortgage1 = ER53050 if ER53050 < 99998
gen mortgage2 = ER53071 if ER53071 < 99998
egen mortgage = rowtotal(mortgage1 mortgage2)

*AUDIT: 1987 people neither pay rent nor have a mortgage. Of those, 1429 report
* owning their home, and the other 558 say that they neither own their home nor rent.
* Presumably these people have paid off their homes.


*AUDIT: Property taxes and insurance premiums. Adjusted to avoid double-counting.
*Values are already annual
gen home_insur = ER53044 if ER53044 < 9998
gen prop_tax = ER53042 if ER53042 < 99998

replace prop_tax = 0 if ER53052 == 1 | ER53073 == 1
replace home_insur = 0 if ER53053 == 1 | ER53074 == 1


egen hous_util 	= rowtotal(fuel electricity water otrutil furnish homerepair rent mortgage prop_tax home_insur)
su hous_util [w=aw], d

**************************** 4.3 Transportation ********************************

gen carinsur 	= ER53819 if ER53819 < 999998
replace carinsur = carinsur * 12 if ER53820 == 5
sum carinsur [w = aw], d

gen carother 	= ER53822*12 if ER53822 < 999998
gen carrepair 	= ER53823*12 if ER53823 < 99998
gen cargaso 	= ER53824*12 if ER53824 < 99998
gen carpark 	= ER53825*12 if ER53825 < 99998
gen bus 	= ER53826*12 if ER53826 < 99998
gen cab 	= ER53827*12 if ER53827 < 99998
gen otrtrans	= ER53828*12 if ER53828 < 99998

egen transport	= rowtotal(carinsur carother carrepair cargaso carpark bus cab otrtrans)
sum transport [w = aw], d

******************************* 4.4 Education **********************************

gen school1 	= ER53830 if ER53830 < 999998
gen school2 	= ER53832 if ER53832 < 999998
egen school 	= rowtotal(school1 school2)
sum school [w = aw], d

******************************* 4.5 Apparel ************************************

gen clothes 	= ER53843 if ER53843 < 999998
replace clothes = clothes * 12 if ER53844 == 5
sum clothes [w = aw], d

**************************** 4.6 Entertainment *********************************

gen vacation 	= ER53848 if ER53848 < 999998
// tab ER53849
replace vacation = vacation * 12 if ER53849 == 5
sum vacation [w = aw], d

gen recreation	= ER53853 if ER53853 < 999998
// tab ER53854
replace recreation = recreation * 12 if ER53854 == 5
sum recreation [w = aw], d

egen entertain 	= rowtotal(vacation recreation)
sum entertain [w = aw], d

******************************** 4.7 Charity ***********************************

gen charity 	= ER54593 if ER54593 < 999998
sum charity [w = aw], d

******************************** 4.7 Medical ***********************************

gen hospital 	= ER57491 if ER57491 < 999998
gen outpt 	= ER57497 if ER57497 < 999998
gen px 		= ER57503 if ER57503 < 999998

*AUDIT: Added helath insurance
gen health_insur = ER57485 if ER57485 < 99998
replace health_insur = health_insur * 4 if ER57486 == 4
replace health_insur = health_insur * 12 if ER57486 == 5


egen medical 	= rowtotal(hospital outpt px health_insur)
sum medical [w = aw], d


*AUDIT: Added the below

***************************** 4.8 Communication *******************************

sum tele [w=aw], d 


**AUDIT: Total

gen total_consumption = food + hous_util + transport + school + clothes + entertain + charity + medical + tele
sum total_consumption [w=aw], d

*/

